package jdbc.demo.dao.impl;

import jdbc.demo.dao.PenaltiesTypesCopy1Dao;
import jdbc.demo.domain.PenaltiesTypesCopy1;
import jdbc.demo.util.JdbcTool;
import jdbc.demo.util.JdbcTool2;
import jdbc.demo.util.JdbcUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述:
 *
 * @author Zhanggq
 * @date 2019/9/12 10:42
 */
public class PenaltiesTypesCopy1Impl implements PenaltiesTypesCopy1Dao {

    private String driverClassName = "com.mysql.jdbc.Driver";
    private String url = "数据库url";
    private String username = "用户名";
    private String password = "用户密码";

    @Override
    public Long save_v5(PenaltiesTypesCopy1 penaltiesTypesCopy1) {
        String sql = "insert into penalties_types_copy1(id,user_id,create_by,judgment_id) values(?,?,?,?)";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Long id = null;
        try {
            connection = JdbcUtil.getConn_v2();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setLong(1, penaltiesTypesCopy1.getId());
            preparedStatement.setLong(2, penaltiesTypesCopy1.getUserId());
            preparedStatement.setString(3, penaltiesTypesCopy1.getCreateBy());
            int i = 0;
//            i=1/0;//利用异常来模拟停电
            preparedStatement.setLong(4, i);
            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            while (resultSet.next()) {
                id = resultSet.getLong(1);
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            JdbcUtil.close(connection, preparedStatement, null);
        }
        return id;
    }

    @Override
    public void save_v1() {
        String sql = "insert into penalties_types_copy1(id,user_id,create_by,judgment_id) values(18,1,'admin',2)";
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            connection = DriverManager.getConnection("数据库url", "用户名", "用户密码");//获取连接
            statement = connection.createStatement();//创建语句对象
            int count = statement.executeUpdate(sql);//执行SQL
            System.out.println("=============保存===========");
            System.out.println("新增的行数: " + count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//释放资源
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void save_v2() {
        String sql = "insert into penalties_types_copy1(id,user_id,create_by,judgment_id) values(18,1,'admin',2)";
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(this.driverClassName);//加载驱动
            connection = DriverManager.getConnection(this.url, this.username, this.password);//获取连接
            statement = connection.createStatement();//创建语句对象
            int count = statement.executeUpdate(sql);//执行SQL
            System.out.println("=============保存===========");
            System.out.println("新增的行数: " + count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//释放资源
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void save_v3() {
        String sql = "insert into penalties_types_copy1(id,user_id,create_by,judgment_id) values(19,1,'admin',2)";
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcTool.getConnection();
            statement = connection.createStatement();//创建语句对象
            int count = statement.executeUpdate(sql);//执行SQL
            System.out.println("=============保存===========");
            System.out.println("新增的行数: " + count);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//释放资源
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void save_v4() {
        String sql = "insert into penalties_types_copy1(id,user_id,create_by,judgment_id) values(20,1,'admin',2)";
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcTool2.getConnection();
            statement = connection.createStatement();//创建语句对象
            int count = statement.executeUpdate(sql);//执行SQL
            System.out.println("=============保存===========");
            System.out.println("新增的行数: " + count);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//释放资源
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void delete(Long id) {
        String sql = "delete from penalties_types_copy1 where id=?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
//            connection = JdbcUtil.getConn();//贾、连
            connection = JdbcUtil.getConn_v2();
            preparedStatement = connection.prepareStatement(sql);//欲
            //设置参数
            preparedStatement.setLong(1, id);
            preparedStatement.executeUpdate();//执
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, preparedStatement, null);//事
        }

    }

    @Override
    public void delete_v1() {
        String sql = "delete from penalties_types_copy1 where id=18";
        Connection connection = null;
        Statement statement = null;
        try {
            //贾
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            //连
            connection = DriverManager.getConnection("数据库url", "用户名", "用户密码");//获取连接
            //欲
            statement = connection.createStatement();
            //执
            int count = statement.executeUpdate(sql);
            System.out.println("=============删除===========");
            System.out.println("删除的行数: " + count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //事
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void update(Long id, PenaltiesTypesCopy1 penaltiesTypesCopy1) {
        String sq1 = "update penalties_types_copy1(user_id,create_by) set values(?,?) where id=?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
//            connection = JdbcUtil.getConn();
            connection = JdbcUtil.getConn_v2();
            preparedStatement = connection.prepareStatement(sq1);
            preparedStatement.setLong(1, penaltiesTypesCopy1.getUserId());
            preparedStatement.setString(2, penaltiesTypesCopy1.getCreateBy());
            preparedStatement.setLong(3, id);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, preparedStatement, null);
        }
    }

    @Override
    public void update_v1(Long id, PenaltiesTypesCopy1 penaltiesTypesCopy1) {

    }


    @Override
    public PenaltiesTypesCopy1 get(Long id) {
        String sql = "select * from penalties_types_copy1 where id=?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
//            connection = JdbcUtil.getConn();
            connection = JdbcUtil.getConn_v2();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                PenaltiesTypesCopy1 penaltiesTypesCopy1 = new PenaltiesTypesCopy1();
                long id1 = resultSet.getLong("id");
                long userId = resultSet.getLong("user_id");
                String createBy = resultSet.getString("create_by");
                penaltiesTypesCopy1.setId(id1);
                penaltiesTypesCopy1.setUserId(userId);
                penaltiesTypesCopy1.setCreateBy(createBy);
                return penaltiesTypesCopy1;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    @Override
    public PenaltiesTypesCopy1 get_v1() {
        String sql = "select * from penalties_types_copy1 where id=17";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //贾
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            //连
            connection = DriverManager.getConnection("数据库url", "用户名", "用户密码");//获取连接
            //欲
            statement = connection.createStatement();
            //执
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                PenaltiesTypesCopy1 copy1 = new PenaltiesTypesCopy1();
                copy1.setId(resultSet.getLong("id"));
                copy1.setUserId(resultSet.getLong("user_id"));
                copy1.setCreateBy(resultSet.getString("create_by"));
                copy1.setJudgmentId(resultSet.getLong("judgment_id"));
                return copy1;
            }
            System.out.println("=============查询单条记录===========");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //事
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    @Override
    public List<PenaltiesTypesCopy1> list() {
        List<PenaltiesTypesCopy1> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from penalties_types_copy1";
        try {
//            connection = JdbcUtil.getConn();
            connection = JdbcUtil.getConn_v2();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                PenaltiesTypesCopy1 penaltiesTypesCopy1 = new PenaltiesTypesCopy1();
                penaltiesTypesCopy1.setId(resultSet.getLong("id"));
                penaltiesTypesCopy1.setUserId(resultSet.getLong("user_id"));
                penaltiesTypesCopy1.setCreateBy(resultSet.getString("create_by"));
                list.add(penaltiesTypesCopy1);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, preparedStatement, resultSet);
        }
//        return list;
        return null;
    }

    @Override
    public List<PenaltiesTypesCopy1> list_v1() {
        return null;
    }
}
